Drill-Down Inquiry Screen-2 ~ LEARN MS-ACCESS TIPS AND TRICKS

Continuation of Last Week's Post.

Designing the Employee-Wise Order Summary Form

We will start by creating the top-level summary form that displays employee-wise order totals. This form will serve as the entry point for drilling down into the details of each employee’s sales performance.

Create a New Form
- Open your database and create a new blank form in Design View.
- Set its Record Source property to the query or table that summarizes orders by Employee — for example:
  qryEmployeeOrderSummary
  This query should include fields such as:
  - EmployeeID
  - EmployeeName
  - TotalOrders
  - TotalSales
Add Controls
- Add a Combo Box (cboEmployee) to allow selection of an Employee.
- Add Text Boxes to display summary values:
  - txtTotalOrders
  - txtTotalSales
- Add Labels with appropriate captions (e.g., “Employee”, “Total Orders”, “Total Sales”).
Form Properties
- Set the Default View property to Single Form.
- Set Allow Additions, Allow Deletions, and Allow Edits to No (this is a summary form, not for data entry).
- Set the Border Style to Dialog and Pop Up to No.
Add a Refresh Button
- Insert a small Command Button (cmdRefresh) with the caption “Refresh Summary”.
- Add the following VBA code in its Click event to requery the form data:
```
Private Sub cmdRefresh_Click()
    Me.Requery
End Sub
```

This completes the Employee Summary layer — the top level of your drill-down screen.
Next, we’ll design the second-level subform to display Order-Wise Details for the selected employee and link it dynamically to the summary form.

03_Employee_Summary Form

Designing the Employee Summary Form
1. Select the Source Query
  - Open the Database Window (or Navigation Pane in newer versions).
  - Locate and select the query named 03_Employee_Summary.
    This query should contain summary data for each employee, such as:
    - Employee ID
    - Employee Name
    - Number of Orders
    - Total Sales Amount
    - Region or City (if applicable)
2. Create the Form Using the Form Wizard
  - From the Insert menu, choose Form Wizard.
  - Select 03_Employee_Summary as the record source.
  - Add the following fields in this order (or as per your data):
    - EmployeeID
    - EmployeeName
    - OrdersCount
    - TotalSales
  - When prompted for the form layout, choose Tabular Form (Continuous Form).
  - Complete the wizard and give the form a suitable name, such as Employee_Summary.
3. Clean Up the Design
  The Form Wizard may add unnecessary design elements, like background images, grid lines, and shadowed controls.
  - Open the form in Design View.
  - Remove any background image (set the Picture property to None).
  - Select all controls and set:
    - Special Effect → Flat
    - Back Color → White or Transparent
    - Border Style → Solid
  - Adjust the Form Header to include a descriptive title such as “Employee Summary” in a bold label.
4. Enhance Readability
  - Align the text boxes neatly in columns.
  - Set the Alternate Back Color of the Detail section (e.g., a light grey) for easier row reading.
  - Format the TotalSales field as Currency.
  - Set OrderCount as a Number, with no decimal places.
5. Save and Test the Form
  - Save the form as Employee_Summary.
  - Switch to Form View to verify that multiple employee records appear in a continuous list, as shown in the sample.
✅ Next Step:
In the following section, we will design the Order Details Subform that will display order-level data for the employee selected in this summary form.

Right-click to open Large Image in New Tab/Window.
Select all the data fields together by clicking on the left border of the Detail section. Alternatively, click anywhere within the Detail section and drag the mouse over all the controls to select them simultaneously.
Display the Property Sheet (View ->Properties).
Change the following Property Values:
- Enabled = No
- Locked = Yes
- Tab Stop = No
- Back Color = 12632256
- Special Effect = Raised
Expand the Detail Section of the Form down to get enough room to draw a lengthy Command Button.
Draw a Command Button as wide as the full length of all the Fields above, so that we can cover the fields by placing it over them.
Display the Property Sheet of the Command Button.
Change the following Property Values:
- Name = cmd1
- Transparent = Yes
- Hyperlink SubAddress = #
We must make the Command Button's height the same as the Fields above. Click on the Command Button to select it, hold the Shift Key, and click on any one of the Fields above to select it along with the Command Button. Alternatively, you can click and drag over the Command Button and any one of the fields above.
Select Format -> Size and select Shortest, if the Command Button's height is more than the field, or select Tallest to make the Command Button as tall as the fields above.
Drag the Transparent Command Button and place it over the Fields above.
To make sure that the Transparent Command Button stays above all the data fields, select Format -> Bring to Front.
Now, reduce the Detail Section height, but leave a little gap above and below the Data Fields.
Draw a Text Box in the Form Footer Section below in the same position as the TORDERS field in the Detail Section and write the following expression in the Control Source Property:
```
=Sum([TORDERS]) 
```
Change the Caption of the child label to Total Orders.
Create a Label at the Header of the Form and change the Caption value to EMPLOYEE-WISE ORDERS SUMMARY. Change the font size to 12.
Display the Code Module of the Form (View -> Code), copy and paste the following VBA lines into the Module:
```
Private Sub cmd1_Click()
Me.Parent.Refresh
Me.Parent.Tabctl0.Pages(1).SetFocus
End Sub
```
Save the Form with the Name: 03_Employee_Summary.

04_Order_ListQ Form

Select the Query 04_Order_ListQ and create a Tabular Form (continuous Form) as we did at the top.
Change the design to look like the sample Image given below:

Right-click to open Large Image in New Tab/Window.

Click to Enlarge

Select all the fields as we did earlier and change the following Property Values:
- Enabled = No
- Locked = Yes
- Tab Stop = No
- Back Color = 16777215
- Special Effect = Flat
Follow Steps 6 to 8 given above.
Change the following Properties of the Command Button:
- Name = cmdOrder
- Transparent = Yes
- Hyperlink SubAddress = #
Follow Steps 10 to 13 as explained above. Reduce the height of the Detail Section without leaving the gap above and below the fields.
Create a Command Button at the Footer Section of the Form.
Display the Property Sheet of the Command Button and change the following Property Values:
- Name = cmdMain
- Caption = Goto Main
Expand the Header Section of the Form and drag the Field Headings down to get enough room to create a Heading for the Form.
Add a Text Box above the Field Headings and write the following expression in the Control Source Property of the Text Box:
```
= "Order List of " & [EmpName]
```
Display the Code Module of the Form (View -> Code), copy and paste the VBA Code given below into the Module, and save the Form with the name 04_Order_ListQ.

Private Sub cmdMain_Click()
    Me.Parent.Tabctl0.Pages(0).SetFocus
End Sub

Private Sub cmdOrder_Click()
   Me.Parent.Refresh
   Me.Parent.Tabctl0.Pages(2).SetFocus
End Sub

05_Order_DetailQ Form

Select the Query 05_Order_DetailQ and Create a Tabular Form.

Here, we don't need the Transparent Command Button; change the Form design to look like the sample image below.

Right-click to open Large Image in New Tab/Window.
Create a Text Box in the Form Footer below the Quantity field and write the following expression in the Control Source Property:
```
=Sum([Quantity])
```
Create another Text Box in the Form Footer below the ExtendedPrice Column and write the following expression in the Control Source Property:
```
=Sum([ExtendedPrice])
```
Create a Command Button in the Form Footer below the TextBoxes.
Change the following Property Values of the Command Button.
- Name = cmdBack
- Caption = << Back
Display the Code Module of the Form (View -> Code), copy and paste the following lines into the Module:
```
Private Sub cmdBack_Click()
    Me.Parent.TabCtl0.Pages(1).SetFocus
End Sub
```
Save the Form with the Name 05_Order_DetailsQ.

Now, we are ready to design the Main Form Inquiry. Main to assemble all three Sub-Forms on a Tab Control and make the Tab Control Pages invisible.

Inquiry_Main Form

Select the Parameter Table Date_Param, select Form from the Insert Menu, and select Design View from the displayed list.
Select the Tab Control Tool from the Toolbox and draw a Tab Control on the Detail Section of the Form.
Check the sample image given below. The Tab Control will have only two pages, but we need one more page.

Right-click to open Large Image in New Tab/Window.

Click to Enlarge

Click on the Tab Control to select it and select Tab Control Page from the Insert Menu.
You may select each Page of the Tab Control, display its Property Sheet, and change the Caption Property value of Page1, Page2, and Page3 as Summary, Orders, and Order Detail, respectively, if needed. It is used for reference purposes only.
The next step is to drag and drop the Sub-Forms (03_Employees_Summary, 04_Order_ListQ, and 05_Order_DetailQ) one by one, on the Tab Control Pages.
Position the Database Window with the Forms Tab active and the Inquiry_Main Form with the Tab Control side by side.
Drag and drop the 03_Employee_Summary Form on the Tab Control Page 1.
You may delete the child label attached to the subform. Resize both the subform and the tab control to ensure that the contents are displayed properly on the screen. Save the form with the name Inquiry_Main, and open it in Normal View to verify how the information appears in the subform. Check whether any adjustment to the size of the form or tab control, either an increase or a decrease, is required or not. Leave some space between the tab control and the top of the Detail section of the form to insert a few text boxes for the StartDate and EndDate fields, along with two additional text boxes for control purposes. Also, make sure to leave some space for a heading above these controls.
Once you are satisfied with the subform’s dimensions and overall design, click on the subform and open the Property Sheet. Take note of the following property values on a piece of paper — you will need them later to resize and position the other two forms that will be inserted into Page 2 and Page 3 of the Tab Control.
- Top
- Left
- Width
- Height
Right-click on the Sub-Form and select Copy from the displayed Shortcut Menu.
Select Tab Control Page2, press and hold the Shift Key, right-click on the Tab Control Page2, and select Paste from the displayed menu.
The pasted control will be an empty form displaying the Source Object Name of the copied Form with a white background.
Display the Property Sheet of the Form and change the Source Object Property value to 04_Order_ListQ. After this change, the Form will appear on the Tab Control Page 2.
Change the dimension property values to match the ones you noted earlier. Since you copied the form (rather than dragging and dropping it from the Database Window), you only need to update the Top and Left property values — the Width and Height values should already be the same. If they differ, adjust them accordingly.
Follow Steps 9 to 12 above to bring in the 05_Order_DetailQ Form to the third Page of the Tab Control.
Display the Field List (View -> Field List) if it is not visible.
Drag and drop StartDate and EndDate fields above the Tab Control, create labels above and left, and change their Caption values as shown in the design above.
Create a Command Button to the right of the EndDate field and change its property values as given below:
- Name = cmdRefresh
- Caption = <<Refresh Screen
The Date fields we created, along with two additional text boxes that we will add, will be referenced in the queries we designed earlier to filter the data displayed on the forms. Although the refresh action is not strictly required—since the data is refreshed automatically before the results are displayed—it serves as an additional feature that allows the user to manually refresh and update any recent changes.
Create a Text Box to the right of the Command Button and display its property sheet, and change the following Property Values:
- Name = EID
- Control Source = =[03_Employee_Summary].Form!EmployeeID
- Visible = No
Create another Text Box below the earlier one and change the property values as given below.
- Name = OID
- Control Source = =[04_Order_ListQ].Form!OrderID
- Visible = No
Create a heading on top of the Form with the Caption Value SALES PERFORMANCE INQUIRY.
Create a Command Button below the Tab Control and change the following property values:
- Name = cmdQuit
- Caption = Quit
Now, we make the Tab Control disappear, and the Tab Control's appearance will change. It will not look like a Tab Control after the change.
Click on the Tab Control, display the Property sheet, and change the following property values:
Caution: After changing the property values, make sure to click the Save button on the toolbar or choose Save from the File menu to preserve your changes immediately. In Microsoft Office 2000, attempting any other action before saving may cause the form to lock up.
- Back Style = Transparent
- Multirow = No
- Style = None

Display the Code Module of the Form, copy, and paste the following few lines of code into the Module:

Private Sub cmdQuit_Click()
  DoCmd.Close acForm, Me.Name
End Sub

Private Sub cmdRefresh_Click()
   Me.Refresh
End Sub

Private Sub EndDate_LostFocus()
Me.Refresh
End Sub

Private Sub Form_Load()
Me.TabCtl0.Pages(0).SetFocus
End Sub

We will finish the design by setting the Property Values of the Inquiry_Main Form. Display the Property Sheet of the Form and change the following Property Values:
- Allow Edits = Yes
- Allow Deletions = No
- Allow Additions = No
- Data Entry = No
- Scroll Bars = Neither
- Record Selectors = No
- Navigation Buttons = No
- Dividing Lines = No
- Auto Re-size = Yes
- Auto Center = Yes
- Pop up = Yes
- Modal = No
- Border Style = Dialog
- Control Box = Yes
- Min Max Buttons = None
- Close Button = Yes
- Allow Design Changes = Design View Only
Save the Form, open it in Normal View, and try out your creation.
Note: If you encounter any issues while running your design, refer to the downloaded sample database. Compare your design with it to identify and correct any mistakes.